﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WinNemoLib;
using System.Data.SqlClient;
using System.Text;
using System.Data;

namespace ToppingControl.Models
{
   public class Top_Waste
   {
      public class TOP_WASTE_DS
      {
         public string _Topno { get; set; }
         public string _Wsdate { get; set; }
         public string _Matcd { get; set; }
         public string _Reason { get; set; }
         public string _Qty { get; set; }
         public string _Empname { get; set; }
         public string _Empdate { get; set; }
         public string _Shift { get; set; }
         public string _Lot { get; set; }
      }

      public List<TOP_WASTE_DS> SEL_TOP_WAS(string dt)
      {

         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();

         str.AppendLine(" select matcd,reason,sum(qty) qty from " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOP_WASTE ");
         str.AppendLine(" where wsdate=convert(datetime,@dt,120)  group by matcd,reason ");

         SqlCommand cmd = new SqlCommand(str.ToString(), cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;
         cmd.Parameters.Add("@dt", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(dt, WinNemoLib.DateUtil.eLanguage.EN);


         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);

         List<TOP_WASTE_DS> list = new List<TOP_WASTE_DS>();
         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return list;
         }
         for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
         {
            DataRow items = ds.Tables[0].Rows[cuIndex];
            TOP_WASTE_DS model = new TOP_WASTE_DS();
           
            model._Matcd = items["matcd"].ToString();
            model._Qty = items["qty"].ToString();
            model._Reason = items["reason"].ToString();
            list.Add(model);
         }
         return list;
      }

      public List<TOP_WASTE_DS> LIST_WAS(string prdF = "", string prdT = "")
      {

         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();

         str.AppendLine(" select * from " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOP_WASTE ");
         string where = "";
         if ((StrUtil.IsNotEmpty(prdF) && StrUtil.IsNotEmpty(prdT)))
         {
             where = " where wsdate between convert(datetime,@dtF,120) and convert(datetime,@dtT,120) ";
         }
         else if (StrUtil.IsNotEmpty(prdF))
         {
             where = " where wsdate=convert(datetime,@dtF,120) ";
         }



         SqlCommand cmd = new SqlCommand(str.ToString()+where, cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;

         if (StrUtil.IsNotEmpty(prdF))
         {
             cmd.Parameters.Add("@dtF", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(prdF, WinNemoLib.DateUtil.eLanguage.EN);
         }

         if (StrUtil.IsNotEmpty(prdT))
         {
             cmd.Parameters.Add("@dtT", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(prdT, WinNemoLib.DateUtil.eLanguage.EN);
         }


         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);

         List<TOP_WASTE_DS> list = new List<TOP_WASTE_DS>();
         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return list;
         }
         for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
         {
            DataRow items = ds.Tables[0].Rows[cuIndex];
 
            TOP_WASTE_DS model = new TOP_WASTE_DS();
            model._Topno = items["topno"].ToString();
            model._Matcd = items["matcd"].ToString();
            model._Wsdate = DateUtil.ToDisplayDate(items["wsdate"], DateUtil.eLanguage.EN);
            model._Empname = items["empname"].ToString();
            model._Empdate = DateUtil.ToDisplayDateTime(items["empdate"], DateUtil.eLanguage.EN);
            model._Qty = items["qty"].ToString();
            model._Reason = items["reason"].ToString();
            model._Shift = items["shift"].ToString();
            model._Lot = items["complot"].ToString();
            list.Add(model);
         }
         return list;
      }

      public Boolean Ins_Data(TOP_WASTE_DS incoming)
      {
         try
         {
            using (ConnectionWrapper cnn = Nemo.GetDefaultConfiguredDbConnectionWrapper())
            {
               cnn.Open();
               cnn.BeginTransaction();
               StringBuilder comstr = new StringBuilder();
               comstr.AppendLine(" INSERT INTO ");
               comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[TOP_WASTE]");
               comstr.AppendLine(" ([topno], [wsdate], [matcd], [reason],[qty] ,[empname],empdate,shift,complot) ");
               comstr.AppendLine("  VALUES (@topno,convert(datetime, @wsdate,120), @matcd, @reason,@qty, @empname,getdate(),@shift,@complot)");


               CommandWrapper cmd = cnn.CreateCommand();
               cmd.CommandText = comstr.ToString();



               cmd.AddParameterStringWithValue("@wsdate", DateUtil.ToInternalDate(incoming._Wsdate, WinNemoLib.DateUtil.eLanguage.EN));
               cmd.AddParameterStringWithValue("@topno", incoming._Topno);
               cmd.AddParameterStringWithValue("@matcd", incoming._Matcd);
               cmd.AddParameterStringWithValue("@qty", incoming._Qty.Replace(",",""));
               cmd.AddParameterStringWithValue("@reason", incoming._Reason);
               cmd.AddParameterStringWithValue("@empname", incoming._Empname);
               cmd.AddParameterStringWithValue("@shift", incoming._Shift);
               cmd.AddParameterStringWithValue("@complot", incoming._Lot);


               if (cmd.ExecuteNonQuery() <= 0)
               {
                  cnn.RollbackTransaction();
                  return false;
               }
               cnn.CommitTransaction();
               return true;
            }

         }
         catch (Exception ex)
         {
            return false;
            Nemo.Error(ex);
         }

      }



   }
}